﻿using System;
using System.Collections.Generic;
using System.Collections.Concurrent;
using System.Data;
using System.Linq;
using System.Threading;
using System.ComponentModel;
using System.Threading.Tasks;
using DotNetCommon;
using DotNetCommon.Data;
using DotNetCommon.Extensions;
using System.Data.Common;

namespace DBUtil
{
    /// <summary>
    /// 通用数据库访问对象
    /// </summary>
    public abstract partial class DBAccess
    {
        #region Is系列: 判断表/视图/列/存储过程/触发器是否存在

        /// <summary>
        /// 判断指定表或视图中是否有某一列
        /// </summary>
        /// <param name="tableOrViewName">表或视图名</param>
        /// <param name="columnName">列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回列是否存在</returns>
        public async virtual Task<bool> IsColumnExistInTableOrViewAsync(string tableOrViewName, string columnName, CancellationToken cancellationToken = default)
        {
            string sql = $"select count(1) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='{tableOrViewName}' and COLUMN_NAME='{columnName}'";
            var res = await SelectScalarAsync<string>(sql, cancellationToken);
            int r = res.ToWithDefault<int>(0);
            return r > 0;
        }

        /// <summary>
        /// 判断指定表中是否有某一列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回列是否存在</returns>
        public async virtual Task<bool> IsColumnExistInTableAsync(string tableName, string columnName, CancellationToken cancellationToken = default)
        {
            string sql = $"select count(1) from INFORMATION_SCHEMA.COLUMNS c left join INFORMATION_SCHEMA.tables t on c.TABLE_CATALOG=t.TABLE_CATALOG and c.TABLE_SCHEMA=t.TABLE_SCHEMA and c.TABLE_NAME=t.TABLE_NAME  where c.COLUMN_NAME='{columnName}' and  c.TABLE_NAME='{columnName}' and t.TABLE_TYPE='BASE TABLE'";
            var res = await SelectScalarAsync<string>(sql, cancellationToken);
            int r = res.ToWithDefault<int>(0);
            return r > 0;
        }

        /// <summary>
        /// 判断指定视图中是否有某一列
        /// </summary>
        /// <param name="viewName">视图名</param>
        /// <param name="columnName">列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回列是否存在</returns>
        public async virtual Task<bool> IsColumnExistInViewAsync(string viewName, string columnName, CancellationToken cancellationToken = default)
        {
            string sql = $"select count(1) from INFORMATION_SCHEMA.COLUMNS c left join INFORMATION_SCHEMA.tables t on c.TABLE_CATALOG=t.TABLE_CATALOG and c.TABLE_SCHEMA=t.TABLE_SCHEMA and c.TABLE_NAME=t.TABLE_NAME  where c.COLUMN_NAME='{viewName}' and  c.TABLE_NAME='{columnName}' and t.TABLE_TYPE='VIEW'";
            var res = await SelectScalarAsync<string>(sql, cancellationToken);
            int r = res.ToWithDefault<int>(0);
            return r > 0;
        }

        /// <summary>
        /// 判断表或视图是否存在
        /// </summary>
        /// <param name="tableOrViewName">表或视图名</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回表或视图是否存在</returns>
        public async virtual Task<bool> IsTableOrViewExistAsync(string tableOrViewName, CancellationToken cancellationToken = default)
        {
            string sql = string.Format("select count(1) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{0}'", tableOrViewName);
            int r = await SelectScalarAsync<int>(sql, cancellationToken);
            return r > 0;
        }

        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回表是否存在</returns>
        public async virtual Task<bool> IsTableExistAsync(string tableName, CancellationToken cancellationToken = default)
        {
            string sql = string.Format("select count(1) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{0}' and TABLE_TYPE='BASE TABLE'", tableName);
            int r = await SelectScalarAsync<int>(sql, cancellationToken);
            return r > 0;
        }

        /// <summary>
        /// 判断视图是否存在
        /// </summary>
        /// <param name="viewName">视图名</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回视图是否存在</returns>
        public async virtual Task<bool> IsViewExistAsync(string viewName, CancellationToken cancellationToken = default)
        {
            string sql = string.Format("select count(1) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{0}' and TABLE_TYPE='VIEW'", viewName);
            int r = await SelectScalarAsync<int>(sql, cancellationToken);
            return r > 0;
        }

        /// <summary>
        /// 判断存储过程是否存在
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public abstract Task<bool> IsProcedureExistAsync(string procName, CancellationToken cancellationToken = default);

        /// <summary>
        /// 判断触发器是否存在
        /// </summary>
        /// <param name="triggerName">触发器名称</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public abstract Task<bool> IsTriggerExistAsync(string triggerName, CancellationToken cancellationToken = default);
        #endregion

        #region Id和流水号生成控制器

        /// <summary>
        /// 根据指定的表名和列名生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        protected abstract Task<long> InternalNewIdAsync(string tableName, string colName, CancellationToken cancellationToken = default);
        /// <summary>
        /// 根据指定的表名和列名生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public virtual Task<long> NewIdAsync(string tableName, string colName, CancellationToken cancellationToken = default) => InternalNewIdAsync(tableName, colName, cancellationToken);

        /// <summary>
        /// 根据指定的表名和列名批量生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="count">生成的Id的数量</param>
        /// <param name="cancellationToken"></param> 
        /// <returns></returns>
        protected abstract Task<List<long>> InternalNewIdsAsync(string tableName, string colName, int count, CancellationToken cancellationToken = default);

        /// <summary>
        /// 根据指定的表名和列名批量生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="count">生成的Id的数量</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public virtual Task<List<long>> NewIdsAsync(string tableName, string colName, int count, CancellationToken cancellationToken = default)
            => InternalNewIdsAsync(tableName, colName, count, cancellationToken);

        /// <summary>
        /// 重置一个表的Id生成控制,这将删除数据库缓存的生成记录(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        protected virtual async Task InternalResetIdAsync(string tableName, string colName, CancellationToken cancellationToken = default)
        {
            EnsureInitGenerator();
            await DeleteAsync(Settings.DBCacheGeneratorIdTableName, $" and tablename={ParaPrefix}tablename and colname={ParaPrefix}colname", new { tablename = tableName, colname = colName }.ToDictionary(), cancellationToken);
        }
        /// <summary>
        /// 重置一个表的Id生成控制,这将删除数据库缓存的生成记录(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public virtual Task ResetIdAsync(string tableName, string colName, CancellationToken cancellationToken = default)
            => InternalResetIdAsync(tableName, colName, cancellationToken);

        /// <summary>
        /// 根据表名和列名生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        protected abstract Task<string> InternalNewSNOAsync(string tableName, string colName, SerialFormat format, CancellationToken cancellationToken = default);
        /// <summary>
        /// 根据表名和列名生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public virtual Task<string> NewSNOAsync(string tableName, string colName, SerialFormat format, CancellationToken cancellationToken = default) => InternalNewSNOAsync(tableName, colName, format, cancellationToken);

        /// <summary>
        /// 根据表名和列名批量生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="count">生成的流水号的数量</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        protected abstract Task<List<string>> InternalNewSNOsAsync(string tableName, string colName, SerialFormat format, int count, CancellationToken cancellationToken = default);

        /// <summary>
        /// 根据表名和列名批量生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="count">生成的流水号的数量</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public virtual Task<List<string>> NewSNOsAsync(string tableName, string colName, SerialFormat format, int count, CancellationToken cancellationToken = default) => InternalNewSNOsAsync(tableName, colName, format, count, cancellationToken);

        /// <summary>
        /// 重置流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="serialFormat">格式</param>
        /// <param name="cancellationToken"></param>
        protected virtual async Task InternalResetSNOAsync(string tableName, string colName, SerialFormat serialFormat, CancellationToken cancellationToken = default)
        {
            EnsureInitGenerator();
            var objName = ParseObjectName(tableName, DefaultSchema);
            tableName = objName.Name;
            colName = ParseObjectName(colName).Name;
            if (serialFormat != null)
            {
                var text = PrePareForSNO(serialFormat, DateTime.Now, DotNetCommon.Machine.MachineIdString).statictext;
                await DeleteAsync(Settings.DBCacheGeneratorSNOTableName, $" and tablename={ParaPrefix}tablename and colname={ParaPrefix}colname and statictext={ParaPrefix}statictext", new { tablename = tableName, colname = colName, statictext = text }.ToDictionary(), cancellationToken);
            }
            else
            {
                await DeleteAsync(Settings.DBCacheGeneratorSNOTableName, $" and tablename={ParaPrefix}tablename and colname={ParaPrefix}colname", new { tablename = tableName, colname = colName }.ToDictionary(), cancellationToken);
            }
        }
        /// <summary>
        /// 重置流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="serialFormat"></param>
        /// <param name="cancellationToken"></param>
        public virtual Task ResetSNOAsync(string tableName, string colName, SerialFormat serialFormat, CancellationToken cancellationToken = default) => InternalResetSNOAsync(tableName, colName, serialFormat, cancellationToken);
        #endregion

        #region 基于数据库的分布式锁 RunInLockAsync

        //private async Task<Result> TryGetLockAsync(string lock_str, string lock_user, long timeoutSecond = 60 * 5, CancellationToken cancellationToken = default)
        //{
        //    return await Result.Wrap(async () =>
        //    {
        //        var starttime = DateTime.Now;
        //        var lockkey = GetLockKey(lock_str, lock_user);
        //        //单机做一次拦截
        //        while (true)
        //        {
        //            if (_locktokens.TryGetValue(lockkey, out CancellationTokenSource token))
        //            {
        //                //指定的锁已经在使用中       
        //                var endtime = DateTime.Now;
        //                var span = endtime - starttime;
        //                if (span.TotalSeconds > timeoutSecond)
        //                {
        //                    throw new Exception($"获取锁[{lock_str}]超时!");
        //                }
        //                else
        //                {
        //                    continue;
        //                }
        //            }
        //            break;
        //        }
        //        //数据库层拦截
        //        await RunInConnectionAsync(async () =>
        //        {
        //            EnsureInitLock();
        //            while (true)
        //            {
        //                try
        //                {
        //                    await ExecuteSqlAsync($"insert into {Settings.DBLockTableName}(lock_str,lock_user) values('{lock_str}','{lock_user}')");
        //                    await StartLockMonitorAync(lock_str, lock_user);
        //                    break;
        //                }
        //                catch (Exception ex)
        //                {
        //                    var endtime = DateTime.Now;
        //                    var span = endtime - starttime;
        //                    if (span.TotalSeconds > timeoutSecond)
        //                    {
        //                        throw new Exception($"获取锁[{lock_str}]超时:{ex?.Message}]", ex);
        //                    }
        //                    await Task.Delay(1000);
        //                    //删除30秒之前的
        //                    await ExecuteSqlAsync($"delete from {Settings.DBLockTableName} where lock_time <{Expression_DateTime.GetCurrentAddSecond(-30)}");
        //                }
        //            }
        //        });
        //    });
        //}

        //private async Task StartLockMonitorAync(string lock_str, string lock_user)
        //{
        //    var cancellationTokenSource = new CancellationTokenSource();
        //    _locktokens.TryAdd(GetLockKey(lock_str, lock_user), cancellationTokenSource);
        //    await Task.Run(async () =>
        //    {
        //        DBAccess db2 = CreateNewDB();
        //        while (true)
        //        {
        //            if (cancellationTokenSource.IsCancellationRequested) break;
        //            //休息10秒
        //            Thread.Sleep(1000 * 10);
        //            //将当前的锁刷新时长
        //            int count = await db2.ExecuteSqlAsync($"update {Settings.DBLockTableName} set lock_time= {Expression_DateTime.Current} where lock_str='{lock_str}' and lock_user='{lock_user}'");
        //            if (count != 1) break;
        //        }
        //    });
        //}

        ///// <summary>
        ///// 在分布式锁内执行代码逻辑
        ///// </summary>
        ///// <param name="lock_str">锁定的资源名称</param>
        ///// <param name="action">要执行的代码逻辑</param>
        ///// <param name="getLockTimeoutSecond">获取锁的超时时间(秒),默认5分钟</param>
        ///// <param name="cancellationToken"></param>
        //public async Task RunInLockAsync(string lock_str, Func<Task> action, long getLockTimeoutSecond = 60 * 5, CancellationToken cancellationToken = default)
        //{
        //    if (action == null) throw new Exception("指定的逻辑不能为空!");
        //    var lock_user = $"{DateTime.Now.ToCommonStampString()} {Guid.NewGuid().ToString().Replace("-", "").ToLower()}";
        //    var res = await TryGetLockAsync(lock_str, lock_user, getLockTimeoutSecond, cancellationToken);
        //    if (res.Success)
        //    {
        //        try
        //        {
        //            await action();
        //        }
        //        finally
        //        {
        //            await TryReleaseLockAsync(lock_str, lock_user);
        //        }
        //    }
        //    else
        //    {
        //        throw new Exception(res.Message);
        //    }
        //}

        ///// <summary>
        ///// 在分布式锁内执行代码逻辑,如果获取锁超时则抛出异常
        ///// </summary>
        ///// <param name="lock_str">锁定的资源名称</param>
        ///// <param name="func">要执行的代码逻辑</param>
        ///// <param name="getLockTimeoutSecond">获取锁的超时时间(秒),默认5分钟</param>
        ///// <param name="cancellationToken"></param>
        //public async Task<T> RunInLockAsync<T>(string lock_str, Func<Task<T>> func, long getLockTimeoutSecond = 60 * 5, CancellationToken cancellationToken = default)
        //{
        //    if (func == null) throw new Exception("指定逻辑不能为空!");
        //    var lock_user = $"{DateTime.Now.ToCommonStampString()} {Guid.NewGuid().ToString().Replace("-", "").ToLower()}";
        //    var res = await TryGetLockAsync(lock_str, lock_user, getLockTimeoutSecond, cancellationToken);
        //    if (res.Success)
        //    {
        //        try
        //        {
        //            return await func();
        //        }
        //        finally
        //        {
        //            await TryReleaseLockAsync(lock_str, lock_user);
        //        }
        //    }
        //    else
        //    {
        //        throw new TimeoutException(res.Message);
        //    }
        //}

        //private async Task TryReleaseLockAsync(string lock_str, string lock_user)
        //{
        //    var lockkey = GetLockKey(lock_str, lock_user);
        //    EnsureInitLock();
        //    if (_locktokens.TryGetValue(lockkey, out CancellationTokenSource token))
        //    {
        //        token.Cancel();
        //        _locktokens.TryRemove(lockkey, out CancellationTokenSource token2);
        //    }
        //    await ExecuteSqlAsync($"delete from {Settings.DBLockTableName} where lock_str='{lock_str}' and lock_user='{lock_user}'");
        //}
        #endregion
    }
}
